<!DOCTYPE html>


<html lang="en">
  

    <head>
      <meta charset="utf-8" />
        
      <meta
        name="viewport"
        content="width=device-width, initial-scale=1, maximum-scale=1"
      />
      <title>数据库笔记 |  欢迎</title>
  <meta name="generator" content="hexo-theme-ayer">
      
      <link rel="shortcut icon" href="/favicon.ico" />
       
<link rel="stylesheet" href="/dist/main.css">

      <link
        rel="stylesheet"
        href="https://cdn.jsdelivr.net/gh/Shen-Yu/cdn/css/remixicon.min.css"
      />
      
<link rel="stylesheet" href="/css/custom.css">
 
      <script src="https://cdn.jsdelivr.net/npm/pace-js@1.0.2/pace.min.js"></script>
       
 

      <link
        rel="stylesheet"
        href="https://cdn.jsdelivr.net/npm/@sweetalert2/theme-bulma@5.0.1/bulma.min.css"
      />
      <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11.0.19/dist/sweetalert2.min.js"></script>

      <!-- mermaid -->
      
      <style>
        .swal2-styled.swal2-confirm {
          font-size: 1.6rem;
        }
      </style>
    <link rel="alternate" href="/atom.xml" title="欢迎" type="application/atom+xml">
</head>
  </html>
</html>


<body>
  <div id="app">
    
      
    <main class="content on">
      <section class="outer">
  <article
  id="post-数据库/数据库笔记"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h1 class="article-title sea-center" style="border-left:0" itemprop="name">
  数据库笔记
</h1>
 

      
    </header>
     
    <div class="article-meta">
      <a href="/2022/01/22/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AC%94%E8%AE%B0/" class="article-date">
  <time datetime="2022-01-22T12:21:48.000Z" itemprop="datePublished">2022-01-22</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
  </div>
  
<div class="word_count">
    <span class="post-time">
        <span class="post-meta-item-icon">
            <i class="ri-quill-pen-line"></i>
            <span class="post-meta-item-text"> Word count:</span>
            <span class="post-count">5.9k</span>
        </span>
    </span>

    <span class="post-time">
        &nbsp; | &nbsp;
        <span class="post-meta-item-icon">
            <i class="ri-book-open-line"></i>
            <span class="post-meta-item-text"> Reading time≈</span>
            <span class="post-count">22 min</span>
        </span>
    </span>
</div>
 
    </div>
      
    <div class="tocbot"></div>




  
    <div class="article-entry" itemprop="articleBody">
       
  <h2 id="00-基础概念"><a href="#00-基础概念" class="headerlink" title="00-基础概念"></a>00-基础概念</h2><ul>
<li>sql：标准的结构化查询语言（由DBMS编译完成）</li>
<li>DB：DataBase（数据库，实际上在硬盘上以文件形式存在）</li>
<li>DBMS：DataBase Management System  （数据库管理系统 MYSQL ORACLE DB2 ….）</li>
</ul>
<h3 id="表"><a href="#表" class="headerlink" title="表"></a>表</h3><ul>
<li>表：table</li>
<li>行：数据 / 记录</li>
<li>列：字段（字段三元素：字段名、数据类型、相关的约束）</li>
</ul>
<h3 id="SQL语句"><a href="#SQL语句" class="headerlink" title="SQL语句"></a>SQL语句</h3><ul>
<li>DQL（数据查询语言）：查询语句、凡是select都是DQL语句 (select)</li>
<li>DML（数据操作语言）：insert delete update 对<strong>表数据</strong>的增删改 (insert delete update)</li>
<li>DDL（数据定义语言）：create drop alter 对<strong>表结构</strong>的增删改 (create drop alter)</li>
<li>TCL（事务控制语言）：commit提交事务，rollback回滚事务</li>
<li>DCL（数据控制语言）：grant授权 revoke撤销权限等</li>
</ul>
<h3 id="DOS命令窗口使用mysql"><a href="#DOS命令窗口使用mysql" class="headerlink" title="DOS命令窗口使用mysql"></a>DOS命令窗口使用mysql</h3><ol>
<li>登录：mysql -u root -p</li>
<li>查看有什么数据库：show databases</li>
<li>创建表：create database 表名</li>
<li>使用表：use 表名</li>
<li>展示表：show tables</li>
<li>查询表结构：desc 表名</li>
<li>初始化数据：执行初始化语句脚本: source 脚本文件路径</li>
</ol>
<h3 id="sql脚本"><a href="#sql脚本" class="headerlink" title="sql脚本"></a>sql脚本</h3><ul>
<li>后缀名为 <strong>.sql</strong></li>
<li>脚本的使用：在DOS命令窗口中： source 脚本路径</li>
</ul>
<h3 id="MYSQL常用命令"><a href="#MYSQL常用命令" class="headerlink" title="MYSQL常用命令"></a>MYSQL常用命令</h3><ul>
<li>select database();    查看当前数据库</li>
<li>\c 结束一条语句</li>
<li>show create table 表名 查看创建表的语句</li>
</ul>
<h3 id="字符串用单引号"><a href="#字符串用单引号" class="headerlink" title="字符串用单引号"></a>字符串用单引号</h3><h3 id="字段值可以参与数值运算"><a href="#字段值可以参与数值运算" class="headerlink" title="字段值可以参与数值运算"></a>字段值可以参与数值运算</h3><h3 id="常见数据类型"><a href="#常见数据类型" class="headerlink" title="常见数据类型"></a>常见数据类型</h3><ul>
<li>int 整数型</li>
<li>bigint 长整型</li>
<li>float 浮点型</li>
<li>char 定长字符串</li>
<li>varchar 可变长字符串</li>
<li>data 日期类型</li>
<li>BLOB 二进制大对象（<strong>存储图片、视频等流媒体信息</strong>）Binary Large OBject</li>
<li>CLOB 字符大对象（存储较大文本，可存储4G字符串） Character Large OBject</li>
</ul>
<h3 id="CRUD"><a href="#CRUD" class="headerlink" title="CRUD"></a>CRUD</h3><ul>
<li>create</li>
<li>retrieve（检索）</li>
<li>update</li>
<li>delete</li>
</ul>
<h2 id="01-简介"><a href="#01-简介" class="headerlink" title="01-简介"></a>01-简介</h2><h3 id="1-什么是SQL"><a href="#1-什么是SQL" class="headerlink" title="1.什么是SQL"></a>1.什么是SQL</h3><p>​    一种<strong>操作数据库</strong>的语言</p>
<h3 id="2-SQL可以做什么？"><a href="#2-SQL可以做什么？" class="headerlink" title="2.SQL可以做什么？"></a>2.SQL可以做什么？</h3><pre><code>* 数据查询、获取、插入、更新、修改、删除
* 创建数据库
* 在数据库中创建表、创建存储过程、创建视图
* 设置表、存储过程、视图的权限
</code></pre>
<h3 id="3-SQL的重要命令"><a href="#3-SQL的重要命令" class="headerlink" title="3.SQL的重要命令"></a>3.SQL的重要命令</h3><ul>
<li>SELECT：从数据库获取数据</li>
<li>UPDATE：更新数据库中的数据</li>
<li>DELETE：从数据库中删除数据</li>
<li>INSERT INTO：向数据库插入新的数据</li>
<li>CREATE DATABASE：创建新数据库</li>
<li>ALTER DATABASE：修改数据库</li>
<li>CREATE TABLE：从创建新表</li>
<li>ALTER TABLE：该表数据库表</li>
<li>DROP TABLE：删除表</li>
<li>CREATE INDEX：创建索引</li>
<li>DROP INDEX：删除索引</li>
</ul>
<h2 id="02-查询、排序、条件语法"><a href="#02-查询、排序、条件语法" class="headerlink" title="02-查询、排序、条件语法"></a>02-查询、排序、条件语法</h2><ol>
<li><p><strong>SELECT</strong> 用于向数据库中选取数据</p>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_name1,column_name2 </span><br><span class="line"><span class="keyword">FROM</span> table_name; </span><br></pre></td></tr></table></figure></li>
<li><p>**SELECT DISTINCT **用于列出不同的值 <strong>去重</strong></p>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> column_name1,column_name2 </span><br><span class="line"><span class="keyword">FROM</span> table_name; </span><br></pre></td></tr></table></figure></li>
<li><p><strong>WHERE</strong> 用于提取满足指定条件的记录</p>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_name,column_name</span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">WHERE</span> column_name operator <span class="keyword">value</span>;</span><br></pre></td></tr></table></figure>

<p>例子：从table1中选出属性country中值为cn的url</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> url </span><br><span class="line"><span class="keyword">FROM</span> TABLE1</span><br><span class="line"><span class="keyword">WHERE</span> country<span class="operator">=</span><span class="string">&#x27;cn&#x27;</span>	</span><br></pre></td></tr></table></figure>

<p>其中opreator的可用运算符有：</p>
<table>
<thead>
<tr>
<th align="left">运算符</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">=</td>
<td align="left">等于</td>
</tr>
<tr>
<td align="left">&lt;&gt;</td>
<td align="left">不等于。<strong>注释：</strong>在 SQL 的一些版本中，该操作符可被写成 !=</td>
</tr>
<tr>
<td align="left">&gt;</td>
<td align="left">大于</td>
</tr>
<tr>
<td align="left">&lt;</td>
<td align="left">小于</td>
</tr>
<tr>
<td align="left">&gt;=</td>
<td align="left">大于等于</td>
</tr>
<tr>
<td align="left">&lt;=</td>
<td align="left">小于等于</td>
</tr>
<tr>
<td align="left">BETWEEN</td>
<td align="left">在某个范围内</td>
</tr>
<tr>
<td align="left">LIKE</td>
<td align="left">搜索某种模式</td>
</tr>
<tr>
<td align="left">IN</td>
<td align="left">指定针对某个列的多个可能值</td>
</tr>
</tbody></table>
</li>
<li><p><strong>OR</strong>和<strong>AND</strong> 用于多过滤条件（AND 优先级大于 OR）</p>
<p>语法+例子：    列出contry为cn 且 年龄大于18 的人的名字</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name</span><br><span class="line"><span class="keyword">FROM</span> people_table</span><br><span class="line"><span class="keyword">WHERE</span> country<span class="operator">=</span><span class="string">&#x27;CN&#x27;</span></span><br><span class="line"><span class="keyword">AND</span> age<span class="operator">&gt;</span><span class="number">18</span></span><br></pre></td></tr></table></figure>

<p>ADN和OR 还可以嵌套使用 比如说 ：列出年龄大于18 国家是cn或者us的人名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name</span><br><span class="line"><span class="keyword">FROM</span> people_table</span><br><span class="line"><span class="keyword">WHERE</span> age<span class="operator">&gt;</span><span class="number">18</span></span><br><span class="line"><span class="keyword">AND</span> (contry<span class="operator">=</span><span class="string">&#x27;cn&#x27;</span> <span class="keyword">OR</span> contry<span class="operator">=</span><span class="string">&#x27;us&#x27;</span>)</span><br></pre></td></tr></table></figure></li>
<li><p><strong>ORDER BY</strong> 用于排序</p>
<p>语法：默认为ASC 升序</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_name,column_name</span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> column_name,column_name <span class="keyword">ASC</span><span class="operator">|</span><span class="keyword">DESC</span>;</span><br></pre></td></tr></table></figure>

<p>例子：    按age的降序 给整个表进行排序</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> </span><br><span class="line"><span class="keyword">FROM</span> table_people</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> age <span class="keyword">DESC</span></span><br></pre></td></tr></table></figure>

<p>例子： 先按照sex进行降序排序 当sex一样的时候，按照age进行升序排序</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line">FORM table_people</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> sex <span class="keyword">DESC</span>, age  </span><br></pre></td></tr></table></figure>

<p>例子：按照所选字段的第1个字段升序排序 <strong>不建议使用</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME AGE</span><br><span class="line"><span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure></li>
<li><p><strong>INSERT INTO</strong> 用于向表插入新记录（也可以插入一个表）</p>
<p>语法： 不指定插入的位置（可以多行插入）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name</span><br><span class="line"><span class="keyword">VALUES</span> (value1,value2,value3,value4...),(value1,value2,value3,value4...),....</span><br></pre></td></tr></table></figure>

<p>语法：指定插入的文位置</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name(column1,column2,column3...)</span><br><span class="line"><span class="keyword">VALUES</span> (value1,value2,value3...)</span><br></pre></td></tr></table></figure>

<p>语法：插入一个表（将表2的左右记录插入到表1中）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> table_name1 <span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> table_name2;</span><br></pre></td></tr></table></figure></li>
<li><p><strong>UPDATE</strong> 用于更新表已存在的记录（修改）</p>
<p>语法：如果没有where 整个表的column1 column2的值都会被改变</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">UPDATE table_name</span><br><span class="line"><span class="keyword">SET</span> column1<span class="operator">=</span>value1,column2<span class="operator">=</span>value2...</span><br><span class="line"><span class="keyword">WHERE</span> some_column<span class="operator">=</span>some_value</span><br></pre></td></tr></table></figure>

<p>例子：更新table_people，将所有contry为us的记录中 其money改为1 name改为son</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">UPDATE table_people</span><br><span class="line"><span class="keyword">SET</span> money<span class="operator">=</span><span class="number">1</span>, name<span class="operator">=</span><span class="string">&#x27;son&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> contry<span class="operator">=</span><span class="string">&#x27;us&#x27;</span></span><br></pre></td></tr></table></figure></li>
<li><p><strong>DELETE</strong> 用于删除表中记录</p>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">WHERE</span> some_column<span class="operator">=</span>some_value</span><br></pre></td></tr></table></figure>

<p>例子：删除 已经死亡 且 国籍为null的记录</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">WHERE</span> (isdead<span class="operator">=</span><span class="number">1</span> <span class="keyword">AND</span> contry<span class="operator">=</span><span class="keyword">null</span>)</span><br></pre></td></tr></table></figure>

<p>例子：删除所有</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="operator">*</span> </span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br></pre></td></tr></table></figure></li>
<li><p><strong>IN</strong> 用于条件筛选的</p>
<p>语法：列出 年龄为18或者36的人名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> AGE <span class="keyword">IN</span> (<span class="number">18</span>,<span class="number">36</span>);</span><br></pre></td></tr></table></figure>

<p>语法：列出年龄不为18和36的人名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> AGE <span class="keyword">NOT</span> <span class="keyword">IN</span> (<span class="number">18</span>,<span class="number">36</span>);</span><br></pre></td></tr></table></figure></li>
<li><p><strong>BETWEEN</strong> 用于条件筛选的</p>
<p>语法：列出年龄在18到36之间的人名（包括18和36） 左边小值 右边大值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> AGE <span class="keyword">BETWEEN</span> <span class="number">18</span> <span class="keyword">AND</span> <span class="number">36</span>；</span><br></pre></td></tr></table></figure></li>
<li><p><strong>LIKE</strong> 用于模糊查询 _代表一个字符 %代表任意多个字符</p>
<p>语法：列出开头为A的人名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> <span class="string">&#x27;A%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>语法：列出第二个字母为A的人名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> <span class="string">&#x27;_A&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>语法: 列出第一个字母为_的人名（<strong>使用转义字符</strong>）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> <span class="string">&#x27;\_&#x27;</span>;</span><br></pre></td></tr></table></figure></li>
<li><p><strong>IS NULL 和 IS NOT NULL</strong></p>
<p>语法：列出国籍为空的人名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> TABLE_NAME</span><br><span class="line"><span class="keyword">WHERE</span> COUNTRY <span class="keyword">IS</span> <span class="keyword">NULL</span>；</span><br></pre></td></tr></table></figure></li>
</ol>
<h2 id="03-SQL语句的执行顺序"><a href="#03-SQL语句的执行顺序" class="headerlink" title="03-SQL语句的执行顺序"></a>03-SQL语句的执行顺序</h2>  <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> 						<span class="number">5</span></span><br><span class="line">​	字段</span><br><span class="line"><span class="keyword">FROM</span>							<span class="number">1</span></span><br><span class="line">​	表名</span><br><span class="line"><span class="keyword">WHERE</span>							<span class="number">2</span></span><br><span class="line">​	条件</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span>					<span class="number">3</span></span><br><span class="line">​	....</span><br><span class="line"><span class="keyword">HAVING</span>						<span class="number">4</span></span><br><span class="line">​	....</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span>						<span class="number">6</span></span><br><span class="line">​	.... </span><br><span class="line">LIMIT 							<span class="number">7</span>（limit语句是<span class="keyword">SQL</span>语句中，最后一个执行的）</span><br><span class="line">​	...</span><br></pre></td></tr></table></figure>

<h2 id="04-处理函数"><a href="#04-处理函数" class="headerlink" title="04-处理函数"></a>04-处理函数</h2><h3 id="1-多行处理函数（分组函数）"><a href="#1-多行处理函数（分组函数）" class="headerlink" title="1.多行处理函数（分组函数）"></a>1.多行处理函数（分组函数）</h3><h4 id="特点"><a href="#特点" class="headerlink" title="特点"></a>特点</h4><ul>
<li>多行处理，一行结果</li>
<li>多行处理函数，自动忽略字段值为NULL的</li>
</ul>
<ul>
<li>不能运用于WHERE子句当中（会1111报错）why？？？<ul>
<li>因为 group by是在执行where之后才执行的</li>
<li>而分组函数 在group by 后面才执行的</li>
<li>于此同时，就解释了为什么叫分组函数</li>
</ul>
</li>
</ul>
<h4 id="类别"><a href="#类别" class="headerlink" title="类别"></a>类别</h4><ul>
<li><p>count(字段值)：计数</p>
<p>语法：统计记录的条数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">FROM</span> TABLE_NAME</span><br></pre></td></tr></table></figure>

<p>语法：统计国籍不为NULL的条数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">count</span>(country) <span class="keyword">FROM</span> TABLE_NAME</span><br></pre></td></tr></table></figure></li>
<li><p>sum(字段值)：求和</p>
<p>语法：求工资总和</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">sum</span>(sal) <span class="keyword">FROM</span> TABLE_NAME</span><br></pre></td></tr></table></figure></li>
<li><p>avg(字段值)：求平均值</p>
</li>
<li><p>max(字段值)：求最大值</p>
</li>
<li><p>min(字段值)：求最小值</p>
</li>
</ul>
<h3 id="2-单行处理函数"><a href="#2-单行处理函数" class="headerlink" title="2.单行处理函数"></a>2.单行处理函数</h3><h4 id="特点-1"><a href="#特点-1" class="headerlink" title="特点"></a>特点</h4><ul>
<li>单行处理、单行结果</li>
</ul>
<h4 id="类别-1"><a href="#类别-1" class="headerlink" title="类别"></a>类别</h4><ul>
<li><p>ifnull(可能为NULL的数据，被当做什么处理)</p>
<p>语法：将字段值为null的设置为‘cn’ 且列出其名字 和 国籍</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name ifnull(country,<span class="string">&#x27;cn&#x27;</span>) <span class="keyword">FROM</span> TABLE_NAME	</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="05-分组查询"><a href="#05-分组查询" class="headerlink" title="05-分组查询"></a>05-分组查询</h2><h3 id="group-by-和-having"><a href="#group-by-和-having" class="headerlink" title="group by 和 having"></a><strong>group by</strong> 和 <strong>having</strong></h3><h4 id="单字段值分组查询"><a href="#单字段值分组查询" class="headerlink" title="单字段值分组查询"></a>单字段值分组查询</h4><ul>
<li><p>语法：</p>
<p>GROUP BY ：按照某个字段或者某些字段进行分组</p>
<p>HAVING：对分组之后的数据进行再次过滤</p>
<p>例子：找出每个工作岗位的最高薪资</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> job,<span class="built_in">max</span>(sal) <span class="keyword">FROM</span> table_name <span class="keyword">GROUP</span> <span class="keyword">BY</span> job;</span><br></pre></td></tr></table></figure>

<p>例子：找出每个国家的最高年龄</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">max</span>(age),country <span class="keyword">FROM</span> table_name <span class="keyword">GROUP</span> <span class="keyword">BY</span> country;</span><br></pre></td></tr></table></figure>

<p>例子：找出每个部门平均薪资，且显示平均薪资大于等于2500的</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">FROM</span> table_name <span class="keyword">GROUP</span> <span class="keyword">BY</span> deptno <span class="keyword">having</span> <span class="built_in">avg</span>(sal)<span class="operator">&gt;</span><span class="number">2500</span></span><br></pre></td></tr></table></figure>

<p>错误例子：name没有意义</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name,<span class="built_in">max</span>(age),country <span class="keyword">FROM</span> table_name <span class="keyword">GROUP</span> <span class="keyword">BY</span> country;</span><br></pre></td></tr></table></figure></li>
<li><p>注意：当sql语句中有group by的时候，select只能跟<strong>分组函数</strong>和<strong>分组字段</strong></p>
</li>
<li><p>当sql语句中，没有group by的时候，整个表会自成一组；</p>
</li>
</ul>
<h4 id="多字段值分组查询"><a href="#多字段值分组查询" class="headerlink" title="多字段值分组查询"></a>多字段值分组查询</h4><ul>
<li><p>例子：找出不同部门中每个工作岗位的最高薪资</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> deptno,job,<span class="built_in">max</span>(sal) <span class="keyword">FROM</span> table_name <span class="keyword">GROUP</span> <span class="keyword">BY</span> deptno,job;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="注意："><a href="#注意：" class="headerlink" title="注意："></a>注意：</h3><p>​    分组进行筛选的时候，<strong>优先考虑</strong>where筛选后再分组 <strong>效率较高</strong></p>
<p>​    分组进行筛选的时候，其次考虑分组后 再用having筛选</p>
<h2 id="06-多表查询"><a href="#06-多表查询" class="headerlink" title="06-多表查询"></a>06-多表查询</h2><h3 id="什么是链接查询？"><a href="#什么是链接查询？" class="headerlink" title="什么是链接查询？"></a>什么是链接查询？</h3><p>​    就是<strong>多表</strong>查询</p>
<h3 id="链接查询的分类"><a href="#链接查询的分类" class="headerlink" title="链接查询的分类"></a>链接查询的分类</h3><ul>
<li><p>按照<strong>语法年代分</strong></p>
<pre><code>* SQL92语法（一些老的DBA还在用）
   * SQL99语法（常用）
</code></pre>
</li>
<li><p>其他</p>
<ul>
<li><p>内连接（inner join INNER可以省略）</p>
<ul>
<li><p>等值链接（体现在限制条件中）</p>
<p>99语法: 查询每个人对应的职位以及部门名称 a表中拥有人和职位和部门编号 b表中拥有部门编号和部门名称</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name a.job , b.depname</span><br><span class="line"><span class="keyword">FROM</span> table_a <span class="keyword">AS</span> a </span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> table_b <span class="keyword">AS</span> b</span><br><span class="line"><span class="keyword">ON</span> a.depno <span class="operator">=</span> b.depno</span><br></pre></td></tr></table></figure>

<p>92语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name a.job , b.name</span><br><span class="line"><span class="keyword">FROM</span> table_a <span class="keyword">AS</span> a , table_b <span class="keyword">AS</span> b</span><br><span class="line"><span class="keyword">WHERE</span> a.depno <span class="operator">=</span> b.depno</span><br></pre></td></tr></table></figure></li>
<li><p>非等值链接（体现在限制条件中）</p>
<p>99语法：    查询每个人的工资等级  a表中有人和工资 b表中有工资等级以及对应的最高最低值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name , b.grade</span><br><span class="line"><span class="keyword">FROM</span> table_a <span class="keyword">AS</span> a </span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> table_b <span class="keyword">AS</span> b</span><br><span class="line"><span class="keyword">ON</span> a.sal <span class="keyword">BETWEEN</span> b.lowest <span class="keyword">AND</span> b.heighest</span><br></pre></td></tr></table></figure>

<p>92语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name , b.grade</span><br><span class="line"><span class="keyword">FROM</span> table_a <span class="keyword">as</span> a , table_b <span class="keyword">as</span> b</span><br><span class="line"><span class="keyword">WHERE</span> a.sal <span class="keyword">BETWEEN</span> b.lowest <span class="keyword">AND</span> b.heighest</span><br></pre></td></tr></table></figure></li>
<li><p>自链接（将一张表 看成 两张表 来链接查询）</p>
<p>99语法：查询每个员工的上级领导 表中有每个员工的编号 员工名字 以及 员工上级的编号</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name , b.name</span><br><span class="line"><span class="keyword">FROM</span> table_a <span class="keyword">AS</span> a </span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> table_a <span class="keyword">AS</span> b</span><br><span class="line"><span class="keyword">ON</span> b.manage <span class="operator">=</span> a.id</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>外连接（outer join OUTER可以省略）</p>
<ul>
<li><p>左外链接（左链接） 左边为主表</p>
<p>99语法：查询出所有员工的上级领导（<strong>相较于自链接而言，不会忽略主表中的a.mange=NULL的记录</strong>）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name , b.name</span><br><span class="line"><span class="keyword">FROM</span> table_a <span class="keyword">as</span> a</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span></span><br><span class="line">table_b <span class="keyword">as</span> b</span><br><span class="line"><span class="keyword">ON</span> a.manAge <span class="operator">=</span> b.id</span><br></pre></td></tr></table></figure></li>
<li><p>右外连接（右链接）</p>
<p>99语法：查询出所有员工的上级领导（<strong>相较于自链接而言，不会忽略主表中的a.mange=NULL的记录</strong>）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> a.name , b.name</span><br><span class="line"><span class="keyword">FROM</span> table_b <span class="keyword">as</span> b</span><br><span class="line"><span class="keyword">RIGHT</span> <span class="keyword">JOIN</span></span><br><span class="line">table_a <span class="keyword">as</span> a</span><br><span class="line"><span class="keyword">ON</span> a.manAge <span class="operator">=</span> b.id</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>全连接 = 左连接 + 右链接 + 去重</p>
<pre><code>* 很少使用，作为了解即可
* FULL OUTER JOIN
</code></pre>
</li>
</ul>
</li>
</ul>
<h3 id="内外链接的区别"><a href="#内外链接的区别" class="headerlink" title="内外链接的区别"></a>内外链接的区别</h3><ul>
<li>内连接：<pre><code>* A,B两表进行内链接查询，    A,B两表处于**平等地位，没有主副之分**
</code></pre>
</li>
<li>外链接：<ul>
<li>A,B两表进行外查询，A,B两表处于<strong>不平等地位，有主副之分</strong>，主要查询主表，捎带查询副表。<strong>当副表中的数据不能和主表的匹配，则副表自动模拟出与主表NULL进行匹配</strong></li>
</ul>
</li>
</ul>
<h3 id="笛卡尔乘积现象"><a href="#笛卡尔乘积现象" class="headerlink" title="笛卡尔乘积现象"></a>笛卡尔乘积现象</h3><p>​    当进行<strong>多表查询时</strong>，如果没有添加限制条件，就会出现<strong>笛卡尔积现象</strong></p>
<h3 id="内外链接、全连接等的Venn-diagram解释："><a href="#内外链接、全连接等的Venn-diagram解释：" class="headerlink" title="内外链接、全连接等的Venn diagram解释："></a>内外链接、全连接等的Venn diagram解释：</h3><blockquote>
<p><a target="_blank" rel="noopener" href="https://blog.csdn.net/u014204541/article/details/79739980">https://blog.csdn.net/u014204541/article/details/79739980</a></p>
</blockquote>
<h3 id="多表查询"><a href="#多表查询" class="headerlink" title="多表查询"></a>多表查询</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">....</span><br><span class="line">a表</span><br><span class="line">(<span class="keyword">left</span><span class="operator">/</span><span class="keyword">right</span>)jion</span><br><span class="line">b表</span><br><span class="line"><span class="keyword">on</span>	条件<span class="number">1</span></span><br><span class="line">(<span class="keyword">left</span><span class="operator">/</span><span class="keyword">right</span>)<span class="keyword">join</span></span><br><span class="line">c表</span><br><span class="line"><span class="keyword">on</span>	条件<span class="number">2</span></span><br><span class="line">...</span><br></pre></td></tr></table></figure>
<p><strong>意为：a表和b表通过条件1链接后，再和c通过条件2链接</strong></p>
<h2 id="07-union"><a href="#07-union" class="headerlink" title="07-union"></a>07-union</h2><h3 id="union：将两个表链接起来"><a href="#union：将两个表链接起来" class="headerlink" title="union：将两个表链接起来"></a>union：将两个表链接起来</h3><ul>
<li><strong>前提条件</strong>：两个表的<strong>列数要一致</strong>，不然会报错</li>
<li>语法：<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> e.name <span class="keyword">from</span> table_a e</span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span> d.name <span class="keyword">from</span> table_b d;</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="08-limit"><a href="#08-limit" class="headerlink" title="08-limit"></a>08-limit</h2><h3 id="limit（重点，分页查询全靠它）"><a href="#limit（重点，分页查询全靠它）" class="headerlink" title="limit（重点，分页查询全靠它）"></a>limit（重点，分页查询全靠它）</h3><ul>
<li>它是mysql特有的语句</li>
<li>作用：用于<strong>提取</strong>结果数据中的<strong>某一部分</strong></li>
<li>语法：limit startIndex,length<br>取出工资前5的人名以及其工资<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> e.name,e.sal</span><br><span class="line"><span class="keyword">from</span> table_a e</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> e.sal <span class="keyword">desc</span></span><br><span class="line">limit <span class="number">0</span>,<span class="number">5</span>;</span><br></pre></td></tr></table></figure>
<h3 id="使用limit进行分页"><a href="#使用limit进行分页" class="headerlink" title="使用limit进行分页"></a>使用limit进行分页</h3></li>
<li>每一页pageSize条记录<br>第pageNo页：limit （ pageNo - 1 ），pageSize；</li>
</ul>
<h2 id="09-表操作"><a href="#09-表操作" class="headerlink" title="09-表操作"></a>09-表操作</h2><h3 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h3><p>​    语法格式：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">	字段名<span class="number">1</span> 数据类型 约束条件，</span><br><span class="line">	字段名<span class="number">2</span> 数据类型 约束条件，</span><br><span class="line">	字段名<span class="number">3</span> 数据类型 约束条件，</span><br><span class="line">	字段名<span class="number">4</span> 数据类型 约束条件，</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h3 id="复制表"><a href="#复制表" class="headerlink" title="复制表"></a>复制表</h3><p>​    语法格式：<strong>将查询到的表 创建成一个新表</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名 <span class="keyword">as</span> <span class="keyword">select</span>语句;</span><br></pre></td></tr></table></figure>
<h3 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h3><p>delete：删除表格上面的数据，但是不删除物理空间<br><strong>删除大表</strong>： truncate table 表名（表被截断，不可回滚，永久丢失，但是保留表头）</p>
<h2 id="10-约束"><a href="#10-约束" class="headerlink" title="10-约束"></a>10-约束</h2><h3 id="约束的分类"><a href="#约束的分类" class="headerlink" title="约束的分类"></a>约束的分类</h3><ul>
<li><p>非空约束（not null）：约束的字段不能为空</p>
</li>
<li><p>唯一约束（unique）：约束的字段不能重复</p>
<ul>
<li><p>单字段约束<strong>（列级约束）</strong>:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">字段 数据类型 <span class="keyword">unique</span>；</span><br></pre></td></tr></table></figure></li>
<li><p>多字段联合约束<strong>（表级约束）</strong>: </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">unique</span>(字段<span class="number">1</span>，字段<span class="number">2.</span>...)；</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>主键约束（primary key）：约束的字段既不能为空，又不能重复</p>
<ul>
<li><p>主键的作用： </p>
<ul>
<li>表的三范式中，第一范式要求表必须有主键</li>
<li>主键值是这行记录在当前表中的唯一标识</li>
</ul>
</li>
<li><p>主键的分类：</p>
<ul>
<li>根据主键字段的<strong>字段数量</strong>来划分<ul>
<li>单一主键（<strong>推荐使用</strong>）</li>
<li>复合主键（多个字段联合添加主键约束）（<strong>不推荐使用，违反了三范式</strong>）</li>
</ul>
</li>
<li>根据主键<strong>性质</strong>来划分<ul>
<li>自然主键（<strong>主键值</strong>最好是一个<strong>和业务没有任何挂钩的自然数</strong>）</li>
<li>业务主键（<strong>不推荐使用</strong>，因为主键最好<strong>不要</strong>跟记录<strong>有关联</strong>。）</li>
</ul>
</li>
</ul>
</li>
<li><p>一个表只能由<strong>一个主键约束</strong></p>
</li>
<li><p><strong>主键值自增</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>外键约束（foreign key）：</p>
<ul>
<li><p>语法:  学生表t_student 中的外键calssno 参考与班级表t_class中的cno</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_student(</span><br><span class="line">	<span class="keyword">foreign</span> key(classno) <span class="keyword">references</span> t_class(cno),</span><br><span class="line">);</span><br></pre></td></tr></table></figure></li>
<li><p>外键<strong>一般引入的是主键</strong>，即使不是主键，也<strong>必须具有唯一性</strong></p>
</li>
</ul>
</li>
<li><p>检查约束（check）：Oracle数据库有，MYSQL没有</p>
</li>
</ul>
<h2 id="11-存储引擎"><a href="#11-存储引擎" class="headerlink" title="11-存储引擎"></a>11-存储引擎</h2><ul>
<li><p><strong>完整的建表语句</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">&#x27;表名&#x27;</span>(</span><br><span class="line">	<span class="string">&#x27;id&#x27;</span> <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span></span><br><span class="line">)ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8</span><br></pre></td></tr></table></figure></li>
<li><p>存储引擎是MYSQL特有的，指的是<strong>表的存储方式</strong></p>
</li>
<li><p>MYSQL的默认存储引擎是InnoDB</p>
</li>
</ul>
<h3 id="MYSQL存储引擎的分类"><a href="#MYSQL存储引擎的分类" class="headerlink" title="MYSQL存储引擎的分类"></a>MYSQL存储引擎的分类</h3><ul>
<li><p>查看当前版本MYSQL支持的存储引擎</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show engines \G</span><br></pre></td></tr></table></figure>
<h3 id="MyISAM"><a href="#MyISAM" class="headerlink" title="MyISAM"></a>MyISAM</h3></li>
<li><p>是MYSQL中最常见的引擎</p>
</li>
<li><p><strong>不支持事务</strong></p>
</li>
<li><p>其用<strong>三个文件</strong>来表示一个表</p>
<ul>
<li>格式文件 — 存储表结构<strong>（.FRM）</strong></li>
<li>数据文件 — 存储表数据<strong>（.MYD）</strong></li>
<li>索引文件 — 存储表索引<strong>（.MYI）</strong></li>
</ul>
</li>
<li><p>优点：</p>
<ul>
<li>灵活的 AUTO_INCREMENT 字段处理</li>
<li>可<strong>被压缩</strong>文件<strong>节省存储空间</strong>，并且可以<strong>转化为只读表</strong>，提高检索效率</li>
</ul>
</li>
<li><p>缺点：</p>
<ul>
<li>不支持事务</li>
</ul>
</li>
</ul>
<h3 id="InnoDB"><a href="#InnoDB" class="headerlink" title="InnoDB"></a>InnoDB</h3><ul>
<li>是MYSQL中的默认引擎，<strong>最安全</strong>    </li>
<li><strong>支持事务</strong></li>
<li>数据库目录用**.FRM<strong>文件格式表示；使用tablespace</strong>存储表内容**，无法被压缩，无法转化为只读</li>
<li>在MySQL服务器<strong>崩溃后提供自动恢复</strong></li>
<li>支持<strong>行级锁</strong>、<strong>级联删除、更新</strong></li>
</ul>
<h3 id="MEMORY"><a href="#MEMORY" class="headerlink" title="MEMORY"></a>MEMORY</h3><ul>
<li>数据库目录用**.FRM**文件格式表示；</li>
<li><strong>表数据、索引</strong>存储在内存中（断电丢失数据）</li>
<li>表级锁</li>
<li>不能包含CLOB BLOB字段</li>
<li><strong>检索很快！</strong></li>
</ul>
<h2 id="12-事务"><a href="#12-事务" class="headerlink" title="12-事务"></a>12-事务</h2><h3 id="什么是事务？"><a href="#什么是事务？" class="headerlink" title="什么是事务？"></a>什么是事务？</h3><ul>
<li><p>一个事务 是 一个<strong>完整的业务逻辑单元</strong>，不可再分</p>
<ul>
<li><p>银行的转账系统，这就是一个事务</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">update t_account <span class="keyword">set</span> balance <span class="operator">=</span> balance <span class="operator">-</span> <span class="number">1000</span> <span class="keyword">where</span> actno <span class="operator">=</span> <span class="string">&#x27;act001&#x27;</span></span><br><span class="line">update t_account <span class="keyword">set</span> balance <span class="operator">=</span> balance <span class="operator">+</span> <span class="number">1000</span> <span class="keyword">where</span> actno <span class="operator">=</span> <span class="string">&#x27;act002&#x27;</span></span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>事务<strong>只包含DML语句</strong>（delete insert update），且其中的DML语句<strong>只能全部成功或者全部失败</strong></p>
</li>
</ul>
<h3 id="事务的特性：ACID"><a href="#事务的特性：ACID" class="headerlink" title="事务的特性：ACID"></a>事务的特性：ACID</h3><ul>
<li>A：<strong>原子性</strong>   事务时最小的工作单元，不可再分</li>
<li>C：<strong>一致性</strong>   事务必须保证多条DML语句<strong>同时成功或同时失败</strong></li>
<li>I：<strong>隔离性</strong>   事务与事务之间必须有隔离</li>
<li>D：<strong>持久性</strong>    最终数据必须<strong>持久化到硬盘文件中</strong>，事务才算成功</li>
</ul>
<h3 id="关于事务的隔离性："><a href="#关于事务的隔离性：" class="headerlink" title="关于事务的隔离性："></a>关于事务的隔离性：</h3><ul>
<li>第一级别：<strong>读未提交</strong>（read uncommitted）<ul>
<li>对方数据还未提交，当前事务就可以读取到对方未提交的数据</li>
<li>缺点：存在脏读（Dirty read）现象。</li>
</ul>
</li>
<li>第二级别：<strong>读已提交</strong>（read committed）<ul>
<li>对方数据提交，当前事务就可以读取到对方已提交的数据</li>
<li>缺点：不可重复读，<strong>不能读取到历史记录，只能读取到最新数据</strong>。存在幻读现象</li>
</ul>
</li>
<li>第三级别：可重复读（repeatable read）<ul>
<li>解决了不可重复读问题</li>
<li>缺点：事务A读取到的数据是开启事务A时的表的内容，若事务B对该表进行操作的话，不影响事务A里面的表的内容。存在幻读现象</li>
</ul>
</li>
<li>第四级别：<strong>序列化读 / 串行化读</strong> （serializeable）<ul>
<li><strong>每次只能处理一个事务</strong>，解决了所有问题</li>
<li>缺点：效率低，事务<strong>需要排队</strong></li>
</ul>
</li>
</ul>
<h3 id="设置事务隔离等级"><a href="#设置事务隔离等级" class="headerlink" title="设置事务隔离等级"></a>设置事务隔离等级</h3><p>​&gt; 设置</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">set global transaction isolation level read uncommitted/其他</span><br></pre></td></tr></table></figure>
<p>​&gt; 查看</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select @@global.tx isolation</span><br></pre></td></tr></table></figure>

<h3 id="oracle默认二档起步，mysql默认三档起步"><a href="#oracle默认二档起步，mysql默认三档起步" class="headerlink" title="oracle默认二档起步，mysql默认三档起步"></a>oracle默认二档起步，mysql默认三档起步</h3><h3 id="事务的原理："><a href="#事务的原理：" class="headerlink" title="事务的原理："></a>事务的原理：</h3><p>利用MYSQL食物日志undolog文件，udolog属于逻辑日志，它记录的是sql执行相关的信息，当回滚发生的时候，数据库会工具undolog的内容做与之前相反的工作。</p>
<p><img src="E:\02-数据库的深入学习\笔记-SQL\事务原理图.png"></p>
<h3 id="事务的操作"><a href="#事务的操作" class="headerlink" title="事务的操作"></a>事务的操作</h3><ul>
<li><p>mysql默认自动提交，所以要通过开启事务，来关闭自动提交</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">start transaction	//用来开启事务</span><br><span class="line">	DML		//sql语句</span><br><span class="line">commit/rollback		//用于结束事务</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="redo日志"><a href="#redo日志" class="headerlink" title="redo日志"></a>redo日志</h3><p>​    防止缓存到硬盘的过程中，只执行了一部分而发生mysql宕机问题。mysql具有redo日志。可以重启mysql后，重新将数据持久化到硬盘中</p>
<h2 id="13-索引"><a href="#13-索引" class="headerlink" title="13-索引"></a>13-索引</h2><h3 id="数据库中的两种检索方式"><a href="#数据库中的两种检索方式" class="headerlink" title="数据库中的两种检索方式"></a>数据库中的两种检索方式</h3><ul>
<li>全表扫描 （效率较低）</li>
<li>根据索引检索（效率较高）</li>
</ul>
<h3 id="什么是索引"><a href="#什么是索引" class="headerlink" title="什么是索引"></a>什么是索引</h3><p>​    <strong>相当于一本书的目录</strong>，可以通过目录快速找到对应的资源</p>
<h3 id="什么时候考虑添加索引"><a href="#什么时候考虑添加索引" class="headerlink" title="什么时候考虑添加索引"></a>什么时候考虑添加索引</h3><ul>
<li>当数据量庞大时</li>
<li>该字段很少的DML操作</li>
<li>该字段<strong>常常出现在where语句中</strong></li>
<li><strong>注意：</strong><ul>
<li>主键会<strong>自动添加索引</strong>，主键索引效率非常高。</li>
<li>unique也会<strong>自动添加索引</strong></li>
</ul>
</li>
</ul>
<h3 id="如何使用索引"><a href="#如何使用索引" class="headerlink" title="如何使用索引"></a>如何使用索引</h3><ul>
<li><p><strong>创建索引对象</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> index 索引名称 <span class="keyword">on</span> 表名（字段名）		</span><br></pre></td></tr></table></figure></li>
<li><p><strong>删除索引对象</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> index 索引名称 <span class="keyword">on</span> 表名</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="索引的分类"><a href="#索引的分类" class="headerlink" title="索引的分类"></a>索引的分类</h3><ul>
<li><strong>单一索引：</strong>单一字段添加索引</li>
<li><strong>复合索引：</strong>多个字段联合添加一个索引</li>
<li><strong>主键索引：</strong>主键会自动添加索引</li>
<li><strong>唯一索引：</strong>unique也会自动添加索引</li>
</ul>
<h3 id="索引的实现原理"><a href="#索引的实现原理" class="headerlink" title="索引的实现原理"></a>索引的实现原理</h3><ol>
<li>添加索引后，<strong>底层构建了一个B+Tree</strong>（进行排序、分区等）</li>
<li>通过<strong>树的搜索</strong>，找到目标字段值</li>
<li>通过该目标字段值的<strong>物理地址</strong>，找到整条记录</li>
</ol>
<h3 id="注意：-1"><a href="#注意：-1" class="headerlink" title="注意："></a>注意：</h3><p>​    索引<strong>不是万能</strong>的，索引也需要<strong>维护</strong>！</p>
<p>​    索引的<strong>底层原理</strong>——B + Tree</p>
<p>​    当进行<strong>like模糊查询</strong>的时候，<strong>索引会失效</strong>。</p>
<p>​    如果字段是字符的话 会通过hash函数，使其唯一且可排序</p>
<h2 id="00-索引拓展"><a href="#00-索引拓展" class="headerlink" title="00-索引拓展"></a>00-索引拓展</h2><h3 id="什么是索引-1"><a href="#什么是索引-1" class="headerlink" title="什么是索引"></a>什么是索引</h3><p><strong>快速找到目标字段值</strong>，提高查询速度</p>
<h3 id="索引的分类-1"><a href="#索引的分类-1" class="headerlink" title="索引的分类"></a>索引的分类</h3><ol>
<li>MYSQL<strong>根据存储引擎</strong>对索引进行分类</li>
<li>InnoDB<strong>主键</strong>使用<strong>聚簇索引</strong></li>
<li>MyISAM<strong>所有的索引</strong>都是<strong>非聚簇索引</strong></li>
</ol>
<h3 id="非聚簇索引"><a href="#非聚簇索引" class="headerlink" title="非聚簇索引"></a>非聚簇索引</h3><ol>
<li><p>什么是非聚簇索引    </p>
<ul>
<li>在二叉树存储数据中，叶子存储着数据的<strong>主键值</strong>和<strong>数据所在行地址</strong></li>
</ul>
</li>
<li><p>非聚簇索引工作<strong>原理</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator">/</span><span class="operator">/</span>创建表</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="string">&#x27;stu&#x27;</span>(</span><br><span class="line">	<span class="string">&#x27;id&#x27;</span> <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">not</span> <span class="keyword">null</span>,</span><br><span class="line">)</span><br><span class="line"><span class="operator">/</span><span class="operator">/</span>创建索引</span><br><span class="line"><span class="keyword">create</span> index id_index <span class="keyword">on</span> s(id);</span><br><span class="line"><span class="operator">/</span><span class="operator">/</span>进行查询</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> stu s <span class="keyword">WHERE</span> s.id<span class="operator">=</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>先在索引树找到id </li>
<li>这个索引id存储在叶子上，同时叶子上还会存储着<strong>数据的地址信息</strong></li>
</ul>
</li>
</ol>
<h3 id="聚簇索引"><a href="#聚簇索引" class="headerlink" title="聚簇索引"></a>聚簇索引</h3><ol>
<li>什么是聚簇索引<ul>
<li>在二叉树存储数据中，叶子还<strong>存储着索引</strong>和<strong>其数据</strong></li>
<li>这样的话 就不需要根据数据地址 再去寻找其数据 从而提高了效率</li>
</ul>
</li>
</ol>
<h3 id="聚簇索引和非聚簇索引的区别-以及-存储引擎区别"><a href="#聚簇索引和非聚簇索引的区别-以及-存储引擎区别" class="headerlink" title="聚簇索引和非聚簇索引的区别 以及 存储引擎区别"></a>聚簇索引和非聚簇索引的区别 以及 存储引擎区别</h3><ol>
<li><p>索引类型与存储引擎的关系</p>
<ul>
<li><p>InnoDB可以创建【聚簇索引】和【非聚簇索引】</p>
</li>
<li><p>MyISAM 只可以创建 【非聚簇索引】</p>
</li>
</ul>
</li>
<li><p>叶子节点存储的内容</p>
<ul>
<li>非聚簇索引：存储着【主键值】以及【该主键值数据的地址信息】</li>
<li>聚簇索引：存储着【主键值】以及【该主键的数据信息】</li>
</ul>
</li>
<li><p>关于索引的问题</p>
<ul>
<li><p>MyISAM 允许表中可以<strong>没有主键和索引</strong></p>
</li>
<li><p>InnoDB <strong>自动</strong>将主键设置为聚簇索引 ， 若没有主键，则会将一个<strong>唯一且非空的字段</strong>设置为索引</p>
<p>若在没有 ， 则会<strong>自动生成一个6字节的主键</strong>，但对于用户<strong>不可见</strong></p>
</li>
</ul>
</li>
<li><p>关于自动增长问题</p>
<ul>
<li>MyISAM、InnoDB 自动增长 都必须是<strong>索引</strong></li>
<li>当使用<strong>组合索引</strong>时 ：<ul>
<li>MyISAM：自动增长 不一定是第一列，可以是<strong>多列排序后递增</strong></li>
<li>InnoDB： 自动增长 一定是第一列 </li>
</ul>
</li>
</ul>
</li>
<li><p>关于count函数</p>
<ul>
<li>MyISAM ： 存有<strong>总的行数</strong></li>
<li>InnoDB：没有存储总行数，但是使用where的时候，两者一致</li>
</ul>
</li>
<li><p>关于全文索引</p>
<ul>
<li>MyISAM 支持FULLTEXT类型的全文索引</li>
<li>InnoDB 不支持</li>
</ul>
</li>
<li><p>关于删除表问题</p>
<ul>
<li>InnoDB DELETE：一条一条删除数据；truncate：截断 不可回滚</li>
<li>MyISAM DELETE：会创建<strong>临时表</strong>，在临时表进行删除，然后使用<strong>临时表覆盖原表</strong></li>
</ul>
</li>
</ol>
<h3 id="聚簇索引和非聚簇所以的适用场合"><a href="#聚簇索引和非聚簇所以的适用场合" class="headerlink" title="聚簇索引和非聚簇所以的适用场合"></a>聚簇索引和非聚簇所以的适用场合</h3><ul>
<li><p><strong>大前提</strong>：索引能够大大地提升查询速度，但是对存储空间和CPU使用率影响很大</p>
</li>
<li><p><strong>适用场合参考条件</strong>：</p>
<ul>
<li><p>数据<strong>频繁修改</strong>的表，<strong>不应该设置索引</strong>；</p>
</li>
<li><p>不是经常作为【检索字段】、【分组字段】、【排序字段】的字段，<strong>不应该设置索引</strong>；</p>
</li>
</ul>
</li>
</ul>
<img src='https://pic.imgdb.cn/item/61ebf6702ab3f51d911d1c9d.png'>




<h2 id="14-视图"><a href="#14-视图" class="headerlink" title="14-视图"></a>14-视图</h2><h3 id="什么是视图"><a href="#什么是视图" class="headerlink" title="什么是视图"></a>什么是视图</h3><p>​&gt; 站在不同角度去看数据</p>
<h3 id="视图的使用"><a href="#视图的使用" class="headerlink" title="视图的使用"></a>视图的使用</h3><ul>
<li><p>创建视图：将table_a中的name，age，sex创建为一个视图</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> myview <span class="keyword">as</span> <span class="keyword">select</span> name,age,sex <span class="keyword">from</span> table_a;</span><br></pre></td></tr></table></figure></li>
<li><p>删除视图：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">view</span> myview;</span><br></pre></td></tr></table></figure></li>
<li><p>对视图的操作：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> myview;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="对视图的操作会影响原表数据"><a href="#对视图的操作会影响原表数据" class="headerlink" title="对视图的操作会影响原表数据"></a>对视图的操作会影响原表数据</h3><h3 id="视图的作用"><a href="#视图的作用" class="headerlink" title="视图的作用"></a>视图的作用</h3><pre><code>* 仅仅给数据操作者，其需要操作的数据，隐藏了表的**其他数据**并起到**保密作用**。
</code></pre>
<h2 id="15-数据三范式"><a href="#15-数据三范式" class="headerlink" title="15-数据三范式"></a>15-数据三范式</h2><h3 id="数据三范式的作用："><a href="#数据三范式的作用：" class="headerlink" title="数据三范式的作用："></a>数据三范式的作用：</h3><p>​    <strong>降低数据冗余</strong></p>
<h3 id="第一范式"><a href="#第一范式" class="headerlink" title="第一范式"></a>第一范式</h3><p>​    必须有<strong>主键</strong>，并且<strong>每个字段原子不可分性</strong></p>
<h3 id="第二范式"><a href="#第二范式" class="headerlink" title="第二范式"></a>第二范式</h3><ul>
<li>建立在第一范式的基础之上，所有非主键字段<strong>完全依赖主键</strong>，<strong>不能产生部分依赖</strong>。（不能用于联合主键）</li>
<li>多对多关系的<strong>处理方法</strong>：<strong>三张表，关系表两个外键</strong>。</li>
</ul>
<h3 id="第三范式"><a href="#第三范式" class="headerlink" title="第三范式"></a>第三范式</h3><ul>
<li>建立在第二范式的基础之上，所有非主键字段直接依赖主键，<strong>不能产生传递依赖</strong>。</li>
<li>一对多关系的<strong>处理方法</strong>：<strong>两张表，多的表加外键</strong>。</li>
</ul>
<h3 id="注意：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。"><a href="#注意：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。" class="headerlink" title="注意：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。"></a>注意：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。</h3><ul>
<li>一对一关系处理方法：<ul>
<li>共享主键</li>
<li>外键+唯一（相当于老师所说的<strong>引用</strong>）</li>
</ul>
</li>
</ul>
<h2 id="16-存储过程"><a href="#16-存储过程" class="headerlink" title="16-存储过程"></a>16-存储过程</h2><h3 id="什么是存储过程"><a href="#什么是存储过程" class="headerlink" title="什么是存储过程"></a>什么是存储过程</h3><p>​&gt; 将多个SQL语句封装成一个函数，用函数的形式调用。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER <span class="operator">/</span><span class="operator">/</span>	<span class="comment">-- 设置//为结束符号</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> 存储过程名（参数类型 变量名 变量类型）</span><br><span class="line"><span class="comment">-- 参数类型 </span></span><br><span class="line"><span class="comment">-- in为写入类型参数 存储过程内，外部传进来的值 在内部中操作 对外部的值的本身不受影响</span></span><br><span class="line"><span class="comment">-- out为写出类型参数	存储过程内， 外部传来的值对内部的值没有影响 内部操作 对外部有影响</span></span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">xxxx;</span><br><span class="line">xxxxx;</span><br><span class="line">xxxx;	<span class="comment">-- 存储过程的主体</span></span><br><span class="line"><span class="keyword">end</span>	<span class="operator">/</span><span class="operator">/</span>	<span class="comment">-- 结束语句</span></span><br><span class="line">DELIMITER;	<span class="comment">-- 取消//为结束符号</span></span><br></pre></td></tr></table></figure>

<ul>
<li>如果没有DELIMITER。遇到第一个；就结束了</li>
<li>所以DELIMITER意义为：从现在开始 //为结束符号</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">call</span> 存储过程名称(参数)</span><br></pre></td></tr></table></figure>

<h3 id="mysql变量"><a href="#mysql变量" class="headerlink" title="mysql变量"></a>mysql变量</h3><ul>
<li><p>局部变量</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">declare</span> 变量名 变量类型 [<span class="keyword">default</span> <span class="keyword">value</span>]</span><br><span class="line"><span class="keyword">set</span> c<span class="operator">=</span>a<span class="operator">+</span>b;	<span class="comment">-- 赋值</span></span><br><span class="line"><span class="keyword">select</span> name <span class="keyword">into</span> v_name <span class="keyword">from</span> emp <span class="keyword">where</span> emp_id<span class="operator">=</span><span class="string">&#x27;1&#x27;</span>;	<span class="comment">-- 将获取的值，放在v_name中</span></span><br></pre></td></tr></table></figure>

<ul>
<li>数据的定义，必须放在最前端【在所有数据使用之前】</li>
</ul>
</li>
<li><p>用户变量【在整个数据库实例中有效】</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> @变量名<span class="operator">=</span>变量值</span><br><span class="line"><span class="keyword">select</span> @变量名;<span class="operator">=</span>变量值</span><br><span class="line"><span class="keyword">select</span> @变量名;<span class="operator">=</span>字段名 <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件	<span class="comment">-- 动态加载</span></span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="控制语句"><a href="#控制语句" class="headerlink" title="控制语句"></a>控制语句</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">IF	ELSEIF	<span class="keyword">ELSE</span>	<span class="keyword">END</span> IF;</span><br><span class="line"></span><br><span class="line"><span class="keyword">CASE</span> 判断值</span><br><span class="line"></span><br><span class="line">​	<span class="keyword">WHEN</span> xxx <span class="keyword">THEN</span> xxxx</span><br><span class="line"></span><br><span class="line">​	<span class="keyword">WHEN</span> xxx <span class="keyword">THEN</span> xxx</span><br><span class="line"></span><br><span class="line">​	<span class="keyword">ELSE</span> xxxx</span><br><span class="line"></span><br><span class="line"><span class="keyword">END</span> <span class="keyword">CASE</span></span><br></pre></td></tr></table></figure>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <div class="declare">
      <ul class="post-copyright">
        <li>
          <i class="ri-copyright-line"></i>
          <strong>Copyright： </strong>
          
          Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.
          
        </li>
      </ul>
    </div>
    
    <footer class="article-footer">
       
<div class="share-btn">
      <span class="share-sns share-outer">
        <i class="ri-share-forward-line"></i>
        分享
      </span>
      <div class="share-wrap">
        <i class="arrow"></i>
        <div class="share-icons">
          
          <a class="weibo share-sns" href="javascript:;" data-type="weibo">
            <i class="ri-weibo-fill"></i>
          </a>
          <a class="weixin share-sns wxFab" href="javascript:;" data-type="weixin">
            <i class="ri-wechat-fill"></i>
          </a>
          <a class="qq share-sns" href="javascript:;" data-type="qq">
            <i class="ri-qq-fill"></i>
          </a>
          <a class="douban share-sns" href="javascript:;" data-type="douban">
            <i class="ri-douban-line"></i>
          </a>
          <!-- <a class="qzone share-sns" href="javascript:;" data-type="qzone">
            <i class="icon icon-qzone"></i>
          </a> -->
          
          <a class="facebook share-sns" href="javascript:;" data-type="facebook">
            <i class="ri-facebook-circle-fill"></i>
          </a>
          <a class="twitter share-sns" href="javascript:;" data-type="twitter">
            <i class="ri-twitter-fill"></i>
          </a>
          <a class="google share-sns" href="javascript:;" data-type="google">
            <i class="ri-google-fill"></i>
          </a>
        </div>
      </div>
</div>

<div class="wx-share-modal">
    <a class="modal-close" href="javascript:;"><i class="ri-close-circle-line"></i></a>
    <p>扫一扫，分享到微信</p>
    <div class="wx-qrcode">
      <img src="//api.qrserver.com/v1/create-qr-code/?size=150x150&data=http://example.com/2022/01/22/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AC%94%E8%AE%B0/" alt="微信分享二维码">
    </div>
</div>

<div id="share-mask"></div>  
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/Mysql/" rel="tag">Mysql</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag">数据库</a></li></ul>

    </footer>
  </div>

   
  <nav class="article-nav">
    
      <a href="/2022/01/24/React%E5%AD%A6%E4%B9%A0/React%E9%AB%98%E7%BA%A7%E6%8C%87%E5%BC%95%E7%AC%94%E8%AE%B0%EF%BC%88%E4%B8%80%EF%BC%89/" class="article-nav-link">
        <strong class="article-nav-caption">上一篇</strong>
        <div class="article-nav-title">
          
            React高级指引笔记（一）
          
        </div>
      </a>
    
    
      <a href="/2022/01/22/React%E5%AD%A6%E4%B9%A0/%E3%80%90%E5%90%83%E5%95%A5%E5%91%A2%E3%80%91React-summary/" class="article-nav-link">
        <strong class="article-nav-caption">下一篇</strong>
        <div class="article-nav-title">【吃啥呢】React-summary</div>
      </a>
    
  </nav>

   
<!-- valine评论 -->
<div id="vcomments-box">
  <div id="vcomments"></div>
</div>
<script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/valine@1.4.14/dist/Valine.min.js"></script>
<script>
  new Valine({
    el: "#vcomments",
    app_id: "jeb2pvxjdeJQOj5OG3AUavGv-gzGzoHsz",
    app_key: "6YlB9tQJTstHMM8GOwFu4uyY",
    path: window.location.pathname,
    avatar: "monsterid",
    placeholder: "给我的文章加点评论吧~",
    recordIP: true,
  });
  const infoEle = document.querySelector("#vcomments .info");
  if (infoEle && infoEle.childNodes && infoEle.childNodes.length > 0) {
    infoEle.childNodes.forEach(function (item) {
      item.parentNode.removeChild(item);
    });
  }
</script>
<style>
  #vcomments-box {
    padding: 5px 30px;
  }

  @media screen and (max-width: 800px) {
    #vcomments-box {
      padding: 5px 0px;
    }
  }

  #vcomments-box #vcomments {
    background-color: #fff;
  }

  .v .vlist .vcard .vh {
    padding-right: 20px;
  }

  .v .vlist .vcard {
    padding-left: 10px;
  }
</style>

 
   
     
</article>

</section>
      <footer class="footer">
  <div class="outer">
    <ul>
      <li>
        Copyrights &copy;
        2022
        <i class="ri-heart-fill heart_icon"></i> 子与子乘
      </li>
    </ul>
    <ul>
      <li>
        
      </li>
    </ul>
    <ul>
      <li>
        
        
        <span>
  <span><i class="ri-user-3-fill"></i>Visitors:<span id="busuanzi_value_site_uv"></span></span>
  <span class="division">|</span>
  <span><i class="ri-eye-fill"></i>Views:<span id="busuanzi_value_page_pv"></span></span>
</span>
        
      </li>
    </ul>
    <ul>
      
    </ul>
    <ul>
      
    </ul>
    <ul>
      <li>
        <!-- cnzz统计 -->
        
      </li>
    </ul>
  </div>
</footer>    
    </main>
    <div class="float_btns">
      <div class="totop" id="totop">
  <i class="ri-arrow-up-line"></i>
</div>

<div class="todark" id="todark">
  <i class="ri-moon-line"></i>
</div>

    </div>
    <aside class="sidebar on">
      <button class="navbar-toggle"></button>
<nav class="navbar">
  
  <div class="logo">
    <a href="/"><img src="/images/ayer-side.svg" alt="欢迎"></a>
  </div>
  
  <ul class="nav nav-main">
    
    <li class="nav-item">
      <a class="nav-item-link" href="/">主页</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/archives">归档</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/categories">分类</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/tags">标签</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/friends">友链</a>
    </li>
    
  </ul>
</nav>
<nav class="navbar navbar-bottom">
  <ul class="nav">
    <li class="nav-item">
      
      <a class="nav-item-link nav-item-search"  title="Search">
        <i class="ri-search-line"></i>
      </a>
      
      
    </li>
  </ul>
</nav>
<div class="search-form-wrap">
  <div class="local-search local-search-plugin">
  <input type="search" id="local-search-input" class="local-search-input" placeholder="Search...">
  <div id="local-search-result" class="local-search-result"></div>
</div>
</div>
    </aside>
    <div id="mask"></div>

<!-- #reward -->
<div id="reward">
  <span class="close"><i class="ri-close-line"></i></span>
  <p class="reward-p"><i class="ri-cup-line"></i></p>
  <div class="reward-box">
    
    
  </div>
</div>
    
<script src="/js/jquery-3.6.0.min.js"></script>
 
<script src="/js/lazyload.min.js"></script>

<!-- Tocbot -->
 
<script src="/js/tocbot.min.js"></script>

<script>
  tocbot.init({
    tocSelector: ".tocbot",
    contentSelector: ".article-entry",
    headingSelector: "h1, h2, h3, h4, h5, h6",
    hasInnerContainers: true,
    scrollSmooth: true,
    scrollContainer: "main",
    positionFixedSelector: ".tocbot",
    positionFixedClass: "is-position-fixed",
    fixedSidebarOffset: "auto",
  });
</script>

<script src="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.js"></script>
<link
  rel="stylesheet"
  href="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.css"
/>
<script src="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/js/jquery.justifiedGallery.min.js"></script>

<script src="/dist/main.js"></script>

<!-- ImageViewer -->
 <!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">

    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>

    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">

        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>

        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">

            <div class="pswp__top-bar">

                <!--  Controls are self-explanatory. Order can be changed. -->

                <div class="pswp__counter"></div>

                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>

                <button class="pswp__button pswp__button--share" style="display:none" title="Share"></button>

                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>

                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>

                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                        <div class="pswp__preloader__cut">
                            <div class="pswp__preloader__donut"></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div>
            </div>

            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>

            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>

            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>

        </div>

    </div>

</div>

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/default-skin/default-skin.min.css">
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe-ui-default.min.js"></script>

<script>
    function viewer_init() {
        let pswpElement = document.querySelectorAll('.pswp')[0];
        let $imgArr = document.querySelectorAll(('.article-entry img:not(.reward-img)'))

        $imgArr.forEach(($em, i) => {
            $em.onclick = () => {
                // slider展开状态
                // todo: 这样不好，后面改成状态
                if (document.querySelector('.left-col.show')) return
                let items = []
                $imgArr.forEach(($em2, i2) => {
                    let img = $em2.getAttribute('data-idx', i2)
                    let src = $em2.getAttribute('data-target') || $em2.getAttribute('src')
                    let title = $em2.getAttribute('alt')
                    // 获得原图尺寸
                    const image = new Image()
                    image.src = src
                    items.push({
                        src: src,
                        w: image.width || $em2.width,
                        h: image.height || $em2.height,
                        title: title
                    })
                })
                var gallery = new PhotoSwipe(pswpElement, PhotoSwipeUI_Default, items, {
                    index: parseInt(i)
                });
                gallery.init()
            }
        })
    }
    viewer_init()
</script> 
<!-- MathJax -->

<!-- Katex -->

<!-- busuanzi  -->
 
<script src="/js/busuanzi-2.3.pure.min.js"></script>
 
<!-- ClickLove -->

<!-- ClickBoom1 -->

<!-- ClickBoom2 -->

<!-- CodeCopy -->
 
<link rel="stylesheet" href="/css/clipboard.css">
 <script src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="ri-file-copy-2-line"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-checkbox-circle-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-checkbox-circle-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-time-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-time-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>
 
<!-- CanvasBackground -->

<script>
  if (window.mermaid) {
    mermaid.initialize({ theme: "forest" });
  }
</script>


    
    

  </div>
</body>

</html>